package com.rockontrol.ele.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.apache.log4j.Logger;

import com.rksp.util.DatabaseUtil;
import com.rksp.util.StringUtil;
import com.rockontrol.ele.pojo.Ma火工品台帐明细;
import com.rockontrol.ele.pojo.Ma火工品消耗台帐;
import com.rockontrol.ele.pojo.Ma火工品消耗台帐明细;
import com.rockontrol.ele.pojo.Ma火工品登记台帐;
import com.rockontrol.ele.pojo.Ma火工品雷管消耗明细;
import com.rockontrol.ele.service.MaGunpowderSvc;
import com.rockontrol.ele.service.model.dbtransaction.MaGunpowderTrans;
import com.rockontrol.ele.service.skeleton.MaGunpowderSvcSkeleton;
import com.rockontrol.ele.vo.MaBlankingDetailVO;
import com.rockontrol.ele.vo.MaIedCdt;
import com.rockontrol.ele.vo.MaIedDayVO;
import com.rockontrol.ele.vo.MaIedMonthVO;
import com.rockontrol.ele.vo.MaManagerVO;

public class MaGunpowderSvcImpl extends MaGunpowderSvcSkeleton implements MaGunpowderSvc{
	private MaGunpowderTrans maGunpowderTrans;
	private static Logger log = Logger.getLogger(MaGunpowderSvcImpl.class);
    

	public MaGunpowderTrans getMaGunpowderTrans(){
        return maGunpowderTrans;
    }

    public void setMaGunpowderTrans(MaGunpowderTrans maGunpowderTrans){
        this.maGunpowderTrans = maGunpowderTrans;
    }

    public String createTheGunpowderRegister(Ma火工品登记台帐 ia_) {
		try {
			return maGunpowderTrans.serviceCreateTheGunpowderRegister(ia_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public Ma火工品登记台帐 getTheGunpowderRegister(String id_) {
		try {
			Ma火工品登记台帐 ia=(Ma火工品登记台帐) service.findObj(Ma火工品登记台帐.class, id_);
			ia.setHyList(service.findObjsHQL("from Ma火工品台帐明细 a where a.台帐id='"+id_+"' and a.物资分类名称 like '%炸药%'"));
			ia.setLgList(service.findObjsHQL("from Ma火工品台帐明细 a where a.台帐id='"+id_+"' and a.物资分类名称 like '%雷管%'"));
			return ia;
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public List<Ma火工品登记台帐> getGunpowderRegisters(MaIedCdt cdt) {
		try {
			return service.findObjsHQL(getGunpowderRegistersSql(cdt));
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public String pageInitGetGunpowderRegisters(MaIedCdt cdt_, String roll_) {
		try {
			return pageFind(roll_, getGunpowderRegistersSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}
	
	private String getGunpowderRegistersSql(MaIedCdt cdt_){
		StringBuffer sql=new StringBuffer();
		sql.append("from Ma火工品登记台帐 a where 1=1 ");
		if(cdt_!=null){
			if(!StringUtil.isEmpty(cdt_.getIedDate())){
				sql.append(" and convert(varchar(10),a.下药日期,120) = '").append(cdt_.getIedDate()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.getStartDate())){
				sql.append(" and a.下药日期 >= '").append(cdt_.getStartDate()).append(" 00:00:00' ");
			}
			if(!StringUtil.isEmpty(cdt_.getEndDate())){
				sql.append(" and a.下药日期 <= '").append(cdt_.getEndDate()).append(" 23:59:59' ");
			}
			if(!StringUtil.isEmpty(cdt_.getWarehouseKeeper())){
				sql.append(" and a.井下保管员 = '").append(cdt_.getWarehouseKeeper()).append("' ");
			}
		}
		return sql.toString();
	}
	
	public String pageNumGetGunpowderRegisters(String roll_, Integer num_) {
		try {
			return pageNum(roll_, num_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public Boolean updateTheGunpowderRegister (Ma火工品登记台帐 ia_) {
		try {
			return maGunpowderTrans.serviceUpdateTheGunpowderRegister(ia_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return false;
		}
	}

	public Boolean deleteTheGunpowderRegister(String id_) {
		try {
			return maGunpowderTrans.serviceDeleteTheGunpowderRegister(id_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return false;
		}
	}



	public String createTheConsumeRegister(Ma火工品消耗台帐 oisr_) {
		try {
			return maGunpowderTrans.serviceCreateTheConsumeRegister(oisr_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}
	public Ma火工品消耗台帐 getTheConsumeRegister(String id_) {
		try {
			Ma火工品消耗台帐 oisr=(Ma火工品消耗台帐) service.findObj(Ma火工品消耗台帐.class, id_);
			List<Ma火工品消耗台帐明细> maFList = new ArrayList<Ma火工品消耗台帐明细>();
			List<Ma火工品消耗台帐明细> maTList = new ArrayList<Ma火工品消耗台帐明细>();
			
			maFList.addAll(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='发放' and a.物资分类名称 like '%炸药%'"));
			maFList.addAll(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='发放' and a.物资分类名称 like '%雷管%'"));
			maTList.addAll(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='清退' and a.物资分类名称 like '%炸药%'"));
			maTList.addAll(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='清退' and a.物资分类名称 like '%雷管%'"));
			
			for (int i=0;i<maFList.size();i++) {
				List<Ma火工品雷管消耗明细> details;
				if(maFList.get(i)!=null && "雷管".equals(maFList.get(i).get物资分类名称())){
					details = service.findObjsHQL("from Ma火工品雷管消耗明细 a  where a.明细id='"+maFList.get(i).getOid()+"'");
					if(details!=null){
						maFList.get(i).setDetail(details);
					}
				}
			}
			
			oisr.setFhyList(maFList);
			oisr.setTlgList(maTList);
			
//			oisr.setFhyList(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='发放' and a.物资分类名称 like '%火药%'"));
//			oisr.setFlgList(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='发放' and a.物资分类名称 like '%雷管%'"));
//			oisr.setFhyList(service.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='清退' and a.物资分类名称 like '%火药%'"));
//			oisr.setFlgList(service.findObjsHQL("from Ma火工品消耗台帐明细 a wheervice.findObjsHQL("from Ma火工品消耗台帐明细 a where a.台帐id='"+id_+"' and a.记录类型='清退' and a.物资分类名称 like '%雷管%'"));
			return oisr;
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public List<Ma火工品消耗台帐> getConsumeRegisters(MaIedCdt cdt_) {
		try {
			return service.findObjsHQL(getConsumeRegiterSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public String pageInitGetConsumeRegisters(MaIedCdt cdt_, String roll_) {
		try {
			return pageFind(roll_, getConsumeRegiterSql(cdt_));
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}
		
	private String getConsumeRegiterSql(MaIedCdt cdt_){
		StringBuffer sql=new StringBuffer();
		sql.append("from Ma火工品消耗台帐 a where 1=1 ");
		if(cdt_!=null){
			if(!StringUtil.isEmpty(cdt_.getIedDate())){
				sql.append(" and convert(varchar(10),a.领用日期,120) = '").append(cdt_.getIedDate()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.getStartDate())){
				sql.append(" and a.领用日期 >= '").append(cdt_.getStartDate()).append(" 00:00:00' ");
			}
			if(!StringUtil.isEmpty(cdt_.getEndDate())){
				sql.append(" and a.领用日期 <= '").append(cdt_.getEndDate()).append(" 23:59:59' ");
			}
			if(!StringUtil.isEmpty(cdt_.getWarehouseKeeper())){
				sql.append(" and a.库管名称 = '").append(cdt_.getWarehouseKeeper()).append("' ");
			}
			if(!StringUtil.isEmpty(cdt_.getUnit())){
				sql.append(" and a.领用单位名称 = '").append(cdt_.getUnit()).append("' ");
			}
		}
		return sql.toString();
	}
	
	public String pageNumConsumeRegisters(String roll_, Integer num_) {
		try {
			return pageNum(roll_, num_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public Boolean updateConsumeRegister(Ma火工品消耗台帐 oisr_) {
		try {
			return maGunpowderTrans.serviceUpdateConsumeRegister(oisr_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return false;
		}
	}

	public Boolean deleteTheConsumeRegister(String id_) {
		try {
			return maGunpowderTrans.serviceDeleteTheConsumeRegister(id_);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return false;
		}
	}

	public List<MaManagerVO> getTheIdeStoreManage(MaIedCdt ia_) {
		//本功能是用报表实现。
		//sql
		/*
SELECT b.领用单位名称,
b.领用日期 领用时间,
SUM(case 物资分类名称 when '火药' then (case 记录类型 when '领用' then 数量 else 0 end) end) 火药领用数量,
SUM(case 物资分类名称 when '火药' then (case 记录类型 when '领用' then 余量 else 0 end) end) 火药余量,
SUM(case 物资分类名称 when '雷管' then (case 记录类型 when '领用' then 数量 else 0 end) end) 雷管领用数量,
SUM(case 物资分类名称 when '雷管' then (case 记录类型 when '领用' then 余量 else 0 end) end) 雷管余量,
SUM(case 物资分类名称 when '火药' then (case 记录类型 when '清退' then 数量 else 0 end) end) 火药清退数量,
SUM(case 物资分类名称 when '火药' then (case 记录类型 when '清退' then 实际消耗数量 else 0 end) end) 火药消耗,
SUM(case 物资分类名称 when '雷管' then (case 记录类型 when '清退' then 数量 else 0 end) end) 雷管清退数量,
SUM(case 物资分类名称 when '雷管' then (case 记录类型 when '清退' then 实际消耗数量 else 0 end) end) 雷管消耗,
REPLACE(REPLACE((SELECT 领用雷管编号+' '+cast(开始编号 as varchar)+'-'+cast(结束编号 as varchar) 编号 FROM MA_火工品雷管消耗明细 N where N.明细ID=
max(case 物资分类名称 when '雷管' then (case 记录类型 when '领用' then a.OID else NULL end) end) FOR XML AUTO ), '<N 编号="', ''), '"/>', '<BR>') 雷管领用明细,
REPLACE(REPLACE((SELECT 领用雷管编号+' '+cast(开始编号 as varchar)+'-'+cast(结束编号 as varchar) 编号 FROM MA_火工品雷管消耗明细 N where N.明细ID=
max(case 物资分类名称 when '雷管' then (case 记录类型 when '清退' then a.OID else NULL end) end) FOR XML AUTO ), '<N 编号="', ''), '"/>', '<BR>') 雷管清退明细,
max(case 记录类型 when '领用' then 区队人员 end) 领用人,
max(case 记录类型 when '领用' then 仓库人员 end) 发放人,
max(case 记录类型 when '清退' then 区队人员 end) 清退人,
max(case 记录类型 when '清退' then 仓库人员 end) 验收人
from MA_火工品消耗台帐明细 a
left join MA_火工品消耗台帐 b on a.台帐ID=b.OID
group by 台帐id,b.领用单位名称,b.领用日期,b.班次
having b.领用日期 between '2012-11-28 00:00:00' and '2012-11-28 23:59:59'
and b.班次='早班'
		 */
		return null;
	}

	public List<MaIedMonthVO> getTheIdeMonthReport(MaIedCdt ia_) {
		try {
			if (ia_==null) {
				return null;
			}
			
			return service.findSQLAlias(getIdeMonthReportSql(ia_), MaIedMonthVO.class);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public List<MaBlankingDetailVO> getIdeBlankingDetail(MaIedCdt ia_) {
		try {
			if (ia_==null) {
				return null;
			}
			return service.findSQLAlias(getIdeBlankingDetailSql(ia_), MaBlankingDetailVO.class);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	public List<MaIedDayVO> getTheIedDayReport(MaIedCdt ia_) {
		//本功能是用报表实现。
		
		return null;
	}

	public List<Ma火工品台帐明细> getTheDetonators(MaIedCdt ia_) {
		try {
			StringBuffer sql=new StringBuffer();
			String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
			if("SQLSERVER".equals(dataBaseType)){
			        //数据库为sqlserver时执行的操作
			    sql.append("select convert(varchar(32),a.oid) oid,convert(varchar(32),a.台帐id) 台帐id,convert(varchar(32),a.物资分类id) 物资分类id,a.物资分类名称,convert(varchar(32),a.物资型号id) 物资型号id,");
	            sql.append(" a.物资型号名称,a.单位,a.数量,a.雷管箱编号,a.雷管盒编号,a.备注 from Ma火工品台帐明细 a left join Ma火工品登记台帐 b on a.台帐id=b.oid where 1=1");
			}else if("ORACLE".equals(dataBaseType)){
			        //数据库为oracle时执行的操作
			    sql.append("select to_char(a.oid) \"oid\",to_char(a.台帐id) \"台帐id\",to_char(a.物资分类id) \"物资分类id\",a.物资分类名称,to_char(a.物资型号id) \"物资型号id\",");
	            sql.append(" a.物资型号名称,a.单位,a.数量,a.雷管箱编号,a.雷管盒编号,a.备注 from Ma火工品台帐明细 a left join Ma火工品登记台帐 b on a.台帐id=b.oid where 1=1");
			}
			if(ia_!=null){
				if(!StringUtil.isEmpty(ia_.getStartDate())){
					sql.append(" and a.下药日期 >= '").append(ia_.getStartDate()).append(" 00:00:00' ");
				}
				if(!StringUtil.isEmpty(ia_.getEndDate())){
					sql.append(" and b.下药日期 <= '").append(ia_.getEndDate()).append(" 23:59:59' ");
				}
				if(!StringUtil.isEmpty(ia_.getWarehouseKeeper())){
					sql.append(" and b.井下保管员 = '").append(ia_.getWarehouseKeeper()).append("' ");
				}
				if(!StringUtil.isEmpty(ia_.getMaType())){
					sql.append(" and a.物资分类名称 like '%").append(ia_.getMaType()).append("%' ");
				}
			}
			return service.findSQL(sql.toString(), Ma火工品台帐明细.class);
		} catch (Exception e) {
			log.error(e.getMessage(),e);
			return null;
		}
	}

	private String getIdeMonthReportSql(MaIedCdt ia_){
        if (ia_==null) {
            return null;
        }
        StringBuffer sql=new StringBuffer();
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        if("SQLSERVER".equals(dataBaseType)){
                //数据库为sqlserver时执行的操作
            sql.append("select blankName,guigStyle,department,accountDate,blankNumber,initNumber,finalNumber,initNumber+blankNumber-finalNumber useNumber  from (select a.物资分类名称 as blankName,a.物资型号名称 guigStyle,a.单位 department,'").append(ia_.getIedDate()).append("' accountDate ,isnull(SUM(数量),0) blankNumber,");
            sql.append(" ISNULL((select top 1 库存量-数量 from MA_火工品台帐明细 s left join MA_火工品登记台帐 w on s.台帐ID=w.OID  where convert(varchar(7),下药日期,120)='").append(ia_.getIedDate()).append("' ");
            sql.append(" and s.物资分类名称=a.物资分类名称 and s.物资型号名称=a.物资型号名称 ");
            sql.append(" order by 下药日期 asc,库存量 asc),0) initNumber,");
            sql.append(" ISNULL((select top 1 库存量 from MA_火工品台帐明细 s left join MA_火工品登记台帐 w on s.台帐ID=w.OID  where convert(varchar(7),下药日期,120)='").append(ia_.getIedDate()).append("' ");
            sql.append(" and s.物资分类名称=a.物资分类名称 and s.物资型号名称=a.物资型号名称 ");
            sql.append(" order by 下药日期 desc,库存量 desc),0) finalNumber");
            sql.append(" from MA_火工品台帐明细 a left join MA_火工品登记台帐 b on a.台帐ID=b.OID ");
            sql.append(" where convert(varchar(7),b.下药日期,120)='").append(ia_.getIedDate()).append("' ");
        }else if("ORACLE".equals(dataBaseType)){
                //数据库为oracle时执行的操作
            sql.append("select blankName,guigStyle,department,accountDate,blankNumber,initNumber,finalNumber,initNumber+blankNumber-finalNumber useNumber  from (select a.物资分类名称 as \"blankName\",a.物资型号名称 \"guigStyle\",a.单位 \"department\",'").append(ia_.getIedDate()).append("' \"accountDate\" ,nvl(SUM(数量),0) \"blankNumber\",");
            sql.append(" nvl((select top 1 库存量-数量 from MA_火工品台帐明细 s left join MA_火工品登记台帐 w on s.台帐ID=w.OID  where convert(varchar(7),下药日期,120)='").append(ia_.getIedDate()).append("' ");
            sql.append(" and s.物资分类名称=a.物资分类名称 and s.物资型号名称=a.物资型号名称 ");
            sql.append(" order by 下药日期 asc,库存量 asc),0) initNumber,");
            sql.append(" nvl((select top 1 库存量 from MA_火工品台帐明细 s left join MA_火工品登记台帐 w on s.台帐ID=w.OID  where convert(varchar(7),下药日期,120)='").append(ia_.getIedDate()).append("' ");
            sql.append(" and s.物资分类名称=a.物资分类名称 and s.物资型号名称=a.物资型号名称 ");
            sql.append(" order by 下药日期 desc,库存量 desc),0) finalNumber");
            sql.append(" from MA_火工品台帐明细 a left join MA_火工品登记台帐 b on a.台帐ID=b.OID ");
            sql.append(" where to_char(b.下药日期,'yyyy-mm')='").append(ia_.getIedDate()).append("' ");
        }
        if(ia_!=null&&!StringUtil.isEmpty(ia_.getMaType()))
        	sql.append("and a.物资分类名称 like '%").append(ia_.getMaType()).append("%'");
        sql.append("group by a.物资分类名称,a.物资型号名称,a.单位) s");
        return sql.toString();
	}
	
	public String pageInitTheIdeMonthReport(MaIedCdt cdt_, String roll_){
	    try {
            return pageFind(roll_, getIdeMonthReportSql(cdt_),new HashMap(),4,MaIedMonthVO.class);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
	}
	
	public String pageNumGetTheIdeMonthReport(String roll_, Integer num_){
	    try {
            return pageNum(roll_, num_);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
	}
	
	public String pageInitTheIdeBlankingDetail(MaIedCdt cdt_, String roll_){
	    try {
            return pageFind(roll_, getIdeBlankingDetailSql(cdt_),new HashMap(),4,MaBlankingDetailVO.class);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
	}
	
	private String getIdeBlankingDetailSql(MaIedCdt ia_){
	    if (ia_==null) {
            return null;
        }
        StringBuffer sql=new StringBuffer();
        String dataBaseType = DatabaseUtil.getDataBaseType(dataSource);
        if("SQLSERVER".equals(dataBaseType)){
                //数据库为sqlserver时执行的操作
        }else if("ORACLE".equals(dataBaseType)){
                //数据库为oracle时执行的操作
        }
//TODO
        sql.append("select a.物资分类名称 as blankName,a.物资型号名称 guigStyle,b.下药日期 blankDate,SUM(数量) blankNum,max(a.库存量) stockNum from MA_火工品台帐明细 a left join MA_火工品登记台帐 b ");
        sql.append(" on a.台帐ID=b.OID ");
        sql.append(" where convert(varchar(7),b.下药日期,120)='").append(ia_.getIedDate()).append("'"); 
        sql.append(" and a.物资分类名称 like '%").append(ia_.getMaType()).append("%'");
        sql.append(" group by a.物资分类名称,a.物资型号名称,b.下药日期");
        return sql.toString();
	}
	
	public String pageNumGetTheIdeBlankingDetail(String roll_, Integer num_){
	    try {
            return pageNum(roll_, num_);
        } catch (Exception e) {
            log.error(e.getMessage(),e);
            return null;
        }
	}

}
